package com.njts.dao;
import com.alibaba.druid.pool.DruidDataSource;
import com.njts.bean.*;
import com.njts.bean.Class;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;


public class UseDao implements Dao {
    JdbcTemplate jdbcTemplate;
    {
        Properties properties=new Properties();
        InputStream resourceAsStream = this.getClass().getResourceAsStream("/jdbc.properties");
        try {
            properties.load(resourceAsStream);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        String driver = properties.getProperty("jdbc.driver");
        String url = properties.getProperty("jdbc.url");
        String userName = properties.getProperty("jdbc.userName");
        String password = properties.getProperty("jdbc.password");

        DruidDataSource druidDataSource=new DruidDataSource();
        druidDataSource.setDriverClassName(driver);
        druidDataSource.setUrl(url);
        druidDataSource.setUsername(userName);
        druidDataSource.setPassword(password);
        this.jdbcTemplate=new JdbcTemplate(druidDataSource);
    }

    @Override
    public User login(String name,String password) {
        String sql="select * from user where name=? and password=?";
        User user=null;
        try {
            user = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(User.class), name, password);
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return user;
    }

    @Override
    public List<Student> selectStudent() {
        String sql="select stu.*,cla.name className from student stu left join class cla on stu.classId=cla.id";
        List<Student> studentList=null;
        try {
            studentList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return studentList;
    }

    @Override
    public List<Teacher> selectTeacher() {
        String sql="select tea.*,cla.name className,sub.name subjectName from teacher tea left join class cla on tea.classId=cla.id left join subject sub on tea.subjectId=sub.id";
        List<Teacher> teacherList=null;
        try{
            teacherList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Teacher.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return teacherList;

    }

    @Override
    public List<Course> selectCourse() {
        String sql="select c.*,t.name teacherName,s.name subjectName,cla.name className from course c left join teacher t on c.teacherId=t.id left join subject s on c.subjectId=s.id left join class cla on cla.id=c.classId";
        List<Course> coursesList=null;
        try{
            coursesList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Course.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return coursesList;
    }

    @Override
    public List<Score> selectScore() {
        String sql="select sco.*,stu.name studentName,sub.name subjectName from score sco left join student stu on sco.studentId=stu.id left join subject sub on sco.subjectId=sub.id";
        List<Score> scoresList=null;
        try{
            scoresList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Score.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return scoresList;
    }

    @Override
    public List<Class> selectClass() {
        String sql="select * from class";
        List<Class> classList=null;
        try{
            classList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Class.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return classList;
    }

    @Override
    public List<Subject> selectSubject() {
        String sql="select * from subject";
        List<Subject> subjectList=null;
        try{
            subjectList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(Subject.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return subjectList;
    }

    @Override
    public List<User> selectUser() {
        String sql="select * from user";
        List<User> userList=null;
        try{
            userList = jdbcTemplate.query(sql,new BeanPropertyRowMapper<>(User.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return userList;
    }

    @Override
    public boolean deleteStudentById(int id) {
        String sql="delete from student where id=?";
        boolean flag=false;
        try{
             flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean insertStudent(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try{
            flag=jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateStudent(String setComplementSql,int id) {
        String sql="update student set"+ setComplementSql +"where id=?";
        boolean flag=false;
        try{
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<Student> selectStudent(String whereComplementSql) {
        String sql="select student.*,class.name as className from student left join class on student.classId=class.id where"+whereComplementSql;
        List<Student> studentList=null;
        try{
            studentList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Student.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return studentList;
    }

    @Override
    public boolean deleteTeacher(int id) {
        String sql="delete from teacher where id=? ";
        boolean flag=false;
        try {
            flag = jdbcTemplate.update(sql, id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateTeacher(String setComplementSql,int id) {
        String sql="update teacher set "+setComplementSql+"where id=?";
        boolean flag=false;
        try {
            flag= jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean insertTeacher(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try {
            flag= jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<Teacher> selectTeacher(String whereComplementSql) {
        String sql="select teacher.*,class.name className,subject.name subjectName from teacher left join class on teacher.classId=class.id left join subject on subject.id=teacher.subjectId where"+whereComplementSql;
        List<Teacher> teacherList;
        try {
            teacherList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Teacher.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return teacherList;
    }

    @Override
    public boolean insertCourse(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateCourse(String setComplementSql, int id) {
        String sql="update course set"+setComplementSql+" where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean deleteCourse(int id) {
        String sql="delete from course where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<Course> selectCourse(String whereComplementSql) {
        String sql="select course.*,teacher.name teacherName,class.name className,subject.name subjectName from course left join teacher on course.teacherId=teacher.id left join class on course.classId=class.id left join subject on course.subjectId=subject.id where"+whereComplementSql;
        List<Course> courseList;
        try {
            courseList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Course.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return courseList;
    }

    @Override
    public boolean insertScore(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean deleteScore(int id) {
        String sql="delete from score where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateScore(String setComplementSql, int id) {
        String sql="update score set "+setComplementSql+" where id=? ";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<Score> selectScore(String whereComplementSql) {
        String sql="select score.*,student.name studentName,subject.name subjectName from score left join student on score.studentId=student.id left join subject on score.subjectId=subject.id where "+whereComplementSql;
        List<Score> scoreList;
        try {
            scoreList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Score.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return scoreList;
    }

    @Override
    public boolean insertClass(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateClass(String setComplementSql, int id) {
        String sql="update class set"+setComplementSql+" where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean deleteClass(int id) {
        String sql="delete from class where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<Class> selectClass(String whereComplementSql) {
        String sql="select * from class where"+ whereComplementSql;
        List<Class> classList=new ArrayList<>();
        try{
            classList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Class.class));

        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return classList;
    }

    @Override
    public boolean insertSubject(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateSubject(String setComplementSql, int id) {
        String sql="update subject set"+setComplementSql+" where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean deleteSubject(int id) {
        String sql="delete from subject where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<Subject> selectSubject(String whereComplementSql) {
        String sql="select * from subject where"+whereComplementSql;
        List<Subject> subjectList=new ArrayList<>();
        try{
            subjectList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(Subject.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return subjectList;
    }

    @Override
    public boolean insertUser(String valuesComplementSql) {
        String sql="insert into"+valuesComplementSql;
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean updateUser(String setComplementSql, int id) {
        String sql="update user set"+setComplementSql+" where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public boolean deleteUser(int id) {
        String sql="delete from user where id=?";
        boolean flag=false;
        try {
            flag=jdbcTemplate.update(sql,id)>0;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return flag;
    }

    @Override
    public List<User> selectUser(String whereComplementSql) {
        String sql="select * from user where"+whereComplementSql;
        List<User> UserList=new ArrayList<>();
        try{
            UserList = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(User.class));
        }catch (Exception e){
            e.printStackTrace();
            return null;
        }
        return UserList;
    }


}
